package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.Person;
import by.bsu.touragency.db.connection.DBConnector;

public class PersonDAO extends AbstractDAO {

	public static final String SQL_CLIENT_AUTORISATION = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.person WHERE ( ( tourAgencyDB.person.login =  ? ) AND (tourAgencyDB.person.password =  ?  ) );";
	public static final String SQL_CLIENT_IS_LOGIN_EXIST = " SELECT COUNT(*) AS rowcount FROM person WHERE ( login  = ? );";
	public static final String SQL_CLIENT_CREATE = " INSERT INTO tourAgencyDB.person ( login , password , email, isAdmin ) VALUES ( ? , ? ,?, ? ) ;";
	public static final String SQL_CLIENT_CREATE_GET_ID = " SELECT * FROM tourAgencyDB.person  WHERE login = ? ;";
	public static final String SQL_CLIENT_READ_BY_ID = "SELECT * FROM tourAgencyDB.person WHERE (id = ? );";
	public static final String SQL_CLIENT_READ_BY_LOGIN = "SELECT * FROM tourAgencyDB.person  WHERE login = ? ;";
	public static final String SQL_CLIENT_UPDATE_PASSWORD = "UPDATE tourAgencyDB.person " + "SET password = ? WHERE ( id = ?);";
	public static final String SQL_CLIENT_UPDATE_EMAIL = "UPDATE tourAgencyDB.person " + "SET email = ? WHERE ( id = ?);";
	public static final String SQL_CLIENT_DELETE_CLIENT = "DELETE FROM tourAgencyDB.person WHERE id = ? ;";
	public static final String SQL_CLIENT_DELETE_BY_ID = "DELETE FROM tourAgencyDB.person WHERE id = ? ;";
	public static final String SQL_CLIENT_DELETE_BY_LOGIN = "DELETE FROM tourAgencyDB.person WHERE login = ? ;";

	private static Logger logger = Logger.getLogger("log");

	public PersonDAO() {
		super();
	}

	public boolean autorisation(String login, String password) {
		try {// 1
			int n = 0;
			connector = new DBConnector();
			try {// 2
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_CLIENT_AUTORISATION);
				try {// 3
					statement.setString(1, login);
					statement.setLong(2, password.hashCode());
					resultSet = statement.executeQuery();
					n = -2;
					if (resultSet.next()) {
						n = resultSet.getInt("rowcount");
					}
				} finally {
				}// 3
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
			} finally {
			} // 2
			if (n == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		} finally {
			logger.debug("Close connection");
			close();
		}

	}

	public boolean isExistByLogin(String login) {
		try {// 1
			logger.info("Is this client login exist");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_IS_LOGIN_EXIST);
			int n;
			try {// 3
				statement.setString(1, login);
				resultSet = statement.executeQuery();
				n = -2;
				resultSet.next();
				n = resultSet.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n == 0) {
				logger.debug("Client with this login already exist");
				return true;
			} else {
				logger.debug("There is no client with this login");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		} finally {
			logger.debug("Close connection");
			close();
		}

	}

	// C
	public Person create(Person client) {
		logger.info("Create client");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_CREATE);
			statement.setString(1, client.getLogin());
			statement.setLong(2, client.getPassword());
			statement.setString(3, client.getEmail());
			statement.setBoolean(4, client.isAdmin());
			statement.executeUpdate();

			statement = connector
					.getPreparedStatement(SQL_CLIENT_CREATE_GET_ID);
			statement.setString(1, client.getLogin());
			resultSet = statement.executeQuery();

			if (resultSet.next()) {
				client.setId(resultSet.getInt("id"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return client;
	}

	// R
	public Person readById(int id) {
		logger.info("Read client by id");
		Person client = new Person();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				client.setId(resultSet.getInt("id"));
				client.setLogin(resultSet.getString("login"));
				client.setPassword(resultSet.getLong("password"));
				client.setEmail(resultSet.getString("email"));
				client.setAdmin(resultSet.getBoolean("isAdmin"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return client;
	}

	public Person readByLogin(String login) {
		logger.info("Read client by login");
		Person client = new Person();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_READ_BY_LOGIN);
			statement.setString(1, login);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				client.setId(resultSet.getInt("id"));
				client.setLogin(resultSet.getString("login"));
				client.setPassword(resultSet.getLong("password"));
				client.setEmail(resultSet.getString("email"));
				client.setAdmin(resultSet.getBoolean("isAdmin"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return client;
	}

	// U
	public void updatePassword(Person client) {
		try {
			logger.info("Update client's password");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_UPDATE_PASSWORD);
			statement.setLong(1, client.getPassword());
			statement.setInt(2, client.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void updateEmail(Person client) {
		try {
			logger.info("Update client's email");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_UPDATE_EMAIL);
			System.out.println(client.getEmail());
			statement.setString(1, client.getEmail());
			statement.setInt(2, client.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	// D
	public void delete(Person client) {
		try {
			logger.info("Delete client");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_DELETE_CLIENT);
			statement.setInt(1, client.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void deleteById(int id) {
		try {
			logger.info("Delete client by id");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_DELETE_BY_ID);
			statement.setInt(1, id);
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void deleteByLogin(String login) {
		try {
			logger.info("Delete client by login");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CLIENT_DELETE_BY_LOGIN);
			statement.setString(1, login);
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

}
